

import org.junit.Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcTest {
  // 数据库连接四要素
  // 1. 驱动
  String driver = "com.mysql.cj.jdbc.Driver";
  // 2. 数据库地址
  String url = "jdbc:mysql://127.0.0.1:3306/briup";
  // 3. 用户名 - 写自己数据库的用户名
  String username = "briup";
  // 4. 密码 - 写自己数据库的密码
  String password = "briup";
  // 获取数据库连接 并输出连接对象
  @Test
  public void getConnection() {
    Connection conn = null;
    try {
      // 1. 注册驱动
      Class.forName(driver);
      // 2. 获取连接对象
      conn = DriverManager.getConnection(url, username, password);
      // 输出连接对象
      System.out.println(conn);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      // 6. 释放资源
      if (conn != null) {
        try {
          conn.close();
        } catch (Exception e) {
          e.printStackTrace();
        }
      }
    }
  }

  // 删除表
  @Test
  public void dropTable() {
    // 数据库连接对象
    Connection conn = null;
    // 数据库操作对象
    Statement stmt = null;
    try {
      // 1. 加载驱动
      Class.forName(driver);
      // 2. 获取连接对象
      conn = DriverManager.getConnection(url, username, password);
      // 3. 获取操作对象
      stmt = conn.createStatement();
      // 4. 执行sql语句
      int rows = stmt.executeUpdate("DROP TABLE IF EXISTS emp");
      // 5. 处理结果
      System.out.println("返回结果rows：" + rows);
    } catch (Exception e) {
      // 释放资源
      if (stmt != null) {
        try {
          stmt.close();
        } catch (Exception err) {
          err.printStackTrace();
        }
      }
      if (conn != null) {
        try {
          conn.close();
        } catch (Exception err) {
          err.printStackTrace();
        }
      }
    }
  }

  // 增加表
  @Test
  public void createTable() {
    // 数据库连接对象
    Connection conn = null;
    // 数据库操作对象
    Statement stmt = null;
    try {
      // 1. 加载驱动
      Class.forName(driver);
      // 2. 获取连接对象
      conn = DriverManager.getConnection(url, username, password);
      // 3. 获取操作对象
      stmt = conn.createStatement();
      // 4. 执行sql语句
      int rows = stmt.executeUpdate("CREATE TABLE t_user(id integer PRIMARY KEY,name varchar(20) not null,age int)");
      // 5. 处理结果
      System.out.println("返回结果rows：" + rows);
    } catch (Exception e) {
      // 释放资源
      if (stmt != null) {
        try {
          stmt.close();
        } catch (Exception err) {
          err.printStackTrace();
        }
      }
      if (conn != null) {
        try {
          conn.close();
        } catch (Exception err) {
          err.printStackTrace();
        }
      }
    }
  }
  
  //插入数据
  @Test 
  public void insertDate(){
    Connection conn=null;
    Statement stmt=null;
    try{
      Class.forName(driver);
      conn=DriverManager.getConnection(url,username,password);
      stmt=conn.createStatement();
      String sql="insert into t_user(id,name,age) values(1,'tom',29)";
      String sql1 ="insert into t_user values(2,'tom',29)";
      int rows=stmt.executeUpdate(sql);
      int rows1 =stmt.executeUpdate(sql1);
      System.out.println("运行结果，修改行数："+ rows1);
    }catch(Exception e){
      if (stmt!=null){
        try {
          stmt.close();
        } catch (Exception exs) {
          exs.printStackTrace();
        }
      }
      if (conn!=null){
        try {
          stmt.close();
        } catch (Exception exs) {
          exs.printStackTrace();
        }
      }
    }
  }
  
  //删除数据
  @Test 
  public void deleteDate(){
    Connection conn=null;
    Statement stmt=null;
    try{
      Class.forName(driver);
      conn=DriverManager.getConnection(url,username,password);
      stmt=conn.createStatement();
      String sql="delete from t_user where id = 2";
      int rows=stmt.executeUpdate(sql);
      System.out.println("运行结果，修稿行数："+rows);
    }catch(Exception e){
      if (stmt!=null){
        try {
          stmt.close();
        } catch (Exception exs) {
          exs.printStackTrace();
        }
      }
      if (conn!=null){
        try {
          stmt.close();
        } catch (Exception exs) {
          exs.printStackTrace();
        }
      }
    }
  }

  // 查询user表中的所有数据
  @Test
  public void getData() {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      // 1. 加载驱动
      Class.forName(driver);
      // 2. 获取连接对象
      conn = DriverManager.getConnection(url, username, password);
      // 3. 获取操作对象
      stmt = conn.createStatement();
      // 4. 执行sql语句
      String sql = "select * from t_user";
      rs = stmt.executeQuery(sql); // 注意这里执行的是查询语句
      // 5. 处理结果
      while (rs.next()) {
        // 通过id获取数据（列名）
        // int id = rs.getInt("id");
        // String name = rs.getString("name");
        // System.out.println("id: " + id+",name:"+name);
        // 通过id获取数据（列的序号，从1开始）
        int id = rs.getInt(1);
        String name = rs.getString(2);
        System.out.println("id: " + id + ",name:" + name);
      }
    } catch (Exception e) {
      // 6. 释放资源
      if (rs != null) {
        try {
          rs.close();
        } catch (Exception exc) {
          exc.printStackTrace();
        }
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (Exception exc) {
          exc.printStackTrace();
        }
      }
      if (conn != null) {
        try {
          conn.close();
        } catch (Exception exc) {
          exc.printStackTrace();
        }
      }
    }
  }

  // 增加密码列
  @Test
  public void addPasswordCol() {
    Connection conn = null;
    Statement stmt = null;
    try {
      // 1. 加载驱动
      Class.forName(driver);
      // 2. 获取连接对象
      conn = DriverManager.getConnection(url, username, password);
      // 3. 获取操作对象
      stmt = conn.createStatement();
      // 删除之前的数据
      // delete form t_user;
      // 4. 执行sql语句
      String sql = "alter table t_user add column password varchar(20) not null after name";
      int rows = stmt.executeUpdate(sql);
      // 5. 处理结果
      System.out.println("修改的行数 rows：" + rows);
    } catch (Exception e) {
      // 6. 释放资源
      if (stmt != null) {
        try {
          stmt.close();
        } catch (Exception exc) {
          exc.printStackTrace();
        }
      }
      if (conn != null) {
        try {
          conn.close();
        } catch (Exception exc) {
          exc.printStackTrace();
        }
      }
    }
  }

  // 登录
  @Test
  public void login() {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      // 1. 加载驱动
      Class.forName(driver);
      // 2. 获取连接对象
      conn = DriverManager.getConnection(url, username, password);
      // 3. 获取操作对象
      stmt = conn.createStatement();
      // 用户输入的内容
      int userId = 1;
      // 用户输入的内容
      String userPwd = "'or '1'='1";
      // 4. 执行sql语句
      String sql = "select count(*) from t_user where id=" + userId + " and password='" + userPwd + "'";
      // String sql = "select count(*) from t_user where id=1 and password='tom'";
      System.out.println("sql:" + sql);
      rs = stmt.executeQuery(sql); // 注意这里执行的是查询语句
      // 5. 处理结果
      while (rs.next()) {
        // 返回值是数值
        int count = rs.getInt(1);
        System.out.println("count: " + count);
        if (count == 0) {
          System.out.println("登录失败");
        } else {
          System.out.println("登录成功");
        }
      }
    } catch (Exception e) {
      // 6. 释放资源
      if (rs != null) {
        try {
          rs.close();
        } catch (Exception exc) {
          exc.printStackTrace();
        }
      }
      if (stmt != null) {
        try {
          stmt.close();
        } catch (Exception exc) {
          exc.printStackTrace();
        }
      }
      if (conn != null) {
        try {
          conn.close();
        } catch (Exception exc) {
          exc.printStackTrace();
        }
      }
    }
  }
}